package sql;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import sql.record.Course;
import sql.record.CourseSchedule;
import sql.record.Registration;
import sql.record.TableRecord;
import sql.record.User;

import logging.Monitor;

/**
 * This class is used to transform Object values to their actual types, when
 * handling JDBC objects such as {@link PreparedStatement} or {@link ResultSet} 
 * @author Ron Galay
 *
 */
enum Conversion {
	String,Integer;
	
	void put(PreparedStatement stmt,int index,Object value) throws SQLException {
		switch(this) {
			case String:
				stmt.setString(index,(java.lang.String) value);
				break;
			case Integer:
				stmt.setInt(index, (java.lang.Integer) value);
		}
	}
	
	Object get(ResultSet rs,String id) throws SQLException {
		Object value = null;
		switch(this) {
			case String:
				value = rs.getString(id);
				break;
			case Integer:
				value =  rs.getInt(id);
		}
		return value;
	}
}

/**
 * This service class organizes all of the database management operations:
 * <ul>
 * 	<li>Creating and destroying a database</li>
 * 	<li>Creating and destroying a database table</li>
 * 	<li>Inserting a record to a database</li>
 * 	<li>Querying for a record</li>
 * 	<li>Removing a record from a database<li>
 * </ul>
 * @author Ron Galay
 *
 */
public class DBManagment {
	
	private static final String URL;
	private static final String USERNAME;
	private static final String PASSWORD;
	private static final String DRIVER;
	
	// static initializer
	static {

		// extracting the relevant data for connection to the MySql server
		URL = "jdbc:mysql://localhost:3306/srgalayDB"; 
		// TODO change to srgalay both the username and the password
		USERNAME = "srgalay";
		PASSWORD = "srgalay"; 
		DRIVER = "com.mysql.jdbc.Driver";
	}
	
	private static Connection createConnection() 
													throws 
													ClassNotFoundException, 
													SQLException {
		
		// load the Driver class. 
		Class.forName(DRIVER); 
		
		// create the connection using the static getConnection method 
		Connection con = DriverManager.getConnection (URL, USERNAME, PASSWORD);
		
		return con;
	}
	
	private static Connection createNonAtomicConnection() throws 
												SQLException, 
												ClassNotFoundException {
		
		Connection con = createConnection();
		con.setAutoCommit(false);
		
		return con;
	}
	
	private static void executeQuery(String query,Connection con) 	
													throws 
													SQLException {
		
	    Statement stmt = null;
		try { 

			// create a Statement class to execute the SQL statement 
			stmt = con.createStatement();
			stmt.execute(query);
			
		} finally { // Close the connection. 
			if(stmt!=null){
				stmt.close();	
			}
		} 
	}
	
	private static void updatePrepStatement( String[] fieldTypes, 
											 Object[] values,
											 String query,
											 Connection con) 
													throws 	SQLException { 
		PreparedStatement pstmt = null;
		try { 

			// create a Statement class to execute the SQL statement 
			pstmt = con.prepareStatement(query);
			// putting the values in the query
			for(int i=0; i<fieldTypes.length; ++i) {
				String type = fieldTypes[i];
				Object value = values[i];
				Conversion converter = Conversion.valueOf(type); 
				converter.put(pstmt,i+1,value);
			}
			pstmt.executeUpdate();
			
		} finally { // Close the connection. 
			if(pstmt!=null){
				pstmt.close();	
			}
		} 
	}
	
	/**
	 * This method creates the predefined db if doesn't already exist 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void createDB() throws ClassNotFoundException, SQLException {
		
		String createDB = QueryFactory.generateCreateDBQuery();
		
		// load the Driver class. 
		Class.forName(DRIVER); 
		
		// create the connection using the static getConnection method 
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306", 
													 USERNAME, PASSWORD);
		try {
			executeQuery(createDB,con);
		} finally {
			if(con!=null){
				con.close();
			}
		}
	}
	
	public static void removeDB() throws ClassNotFoundException, SQLException {
		
		String createDB = QueryFactory.generaeteRemoveDBQuery();
		
		// load the Driver class. 
		Class.forName(DRIVER); 
		
		// create the connection using the static getConnection method 
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306", 
													 USERNAME, PASSWORD);
		try {
			executeQuery(createDB,con);
		} finally {
			if(con!=null){
				con.close();
			}
		}
	}
	
	/**
	 * This method assumes that the predefined db was already created.
	 * It adds the passed record to the table. 
	 * @param record the record object we want to add to the table
	 * @throws ClassNotFoundException
	 * @throws SQLException if the record already exists in the table 
	 */
	public static void createTable(TableRecord record) 	throws 
														ClassNotFoundException,
														SQLException {
		
		String createTable = QueryFactory.generateCreateTableQuery(record);
		String tableName = record.getTableName();
		
		Connection con = createConnection();
		try {
			executeQuery(createTable,con);
		} finally {
			if(con!=null){
				con.close();
			}
		}
		
		Monitor.write(null, "Created the table named "+tableName);
	}
	
	/**
	 * Removes the table from the predefined database.
	 * @param record
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void destroyTable(TableRecord record) throws 
														ClassNotFoundException, 
														SQLException {
		
		String dropTable = QueryFactory.generateDropTableQuery(record);
		String tableName = record.getClass().getSimpleName();
		
		Connection con = createConnection();
		try {
			executeQuery(dropTable,con);
		} finally {
			if(con!=null){
				con.close();
			}
		}
		
		Monitor.write(null, "Dropped the table named "+tableName);
	}
	
	private static void insertRecordAux(List<TableRecord> records) 
														throws 
														SQLException, 
														ClassNotFoundException {
		
		Connection con = createNonAtomicConnection();
		try {
			for(TableRecord record: records) {
				String insert = QueryFactory.generateInsertQuery(record);
				String tableName = record.getClass().getSimpleName();
				updatePrepStatement(record.getArrayType(),
									record.getArrayValues(),
									insert,con);
				
				Monitor.write(null, "Inserted the query to table "+tableName);
			}
			con.commit();
		} catch (SQLException e) {
			con.rollback();
			if(e.getErrorCode() != 1062){
				throw e;
			}else{
				throw new SQLException("alreadyExists");
			}
				
			
		} finally {
			if(con!=null){
				con.close();
			}
		}
	}
	
	/**
	 * Inserting the records in succession as they appear in the list.
	 * The atomicity means, that either all of the queries succeed or they all fail.
	 * @param record list of records we want to insert in atomic manner
	 * @throws ClassNotFoundException
	 * @throws SQLException means that all the queries in the transaction failed
	 */
	public static void insertRecord(List<TableRecord> records) throws 
														ClassNotFoundException, 
														SQLException {
		// we have a special handling for courses and registration to course
		for(TableRecord record:records) {
			
			// special handling for courses
			if(record instanceof Course) {
				insertCourse(records);
				return;
			}
			
			// special handling for registration
			if(record instanceof Registration) {
				insertRegistration(records);
				return;
			}
		}
		
		insertRecordAux(records);
	}
	
	private static synchronized void insertCourse(List<TableRecord> records) 
													throws 
													ClassNotFoundException, 
													SQLException {
		
		// getting the CourseSchedule instance
		List<CourseSchedule> schedules = new LinkedList<CourseSchedule>();
		for(TableRecord record:records) {
			if(record instanceof CourseSchedule) {
				schedules.add((CourseSchedule) record);
			}
		}
		
		// checking that no overlaps exist for the same group
		List<CourseSchedule> allSchedules = getTable(new CourseSchedule());
		for (CourseSchedule courseSchedule : allSchedules) {
			for(CourseSchedule schedule:schedules) {
				if( (courseSchedule.groupNum == schedule.groupNum) &&
					(courseSchedule.day == schedule.day) && 
					(	((courseSchedule.startHour > schedule.startHour) &&
						 (courseSchedule.startHour < schedule.endHour)) ||
						 ((courseSchedule.endHour > schedule.startHour) &&
						 (courseSchedule.endHour < schedule.endHour)) ||
						 ((courseSchedule.startHour <= schedule.startHour) &&
						  (courseSchedule.endHour >= schedule.endHour))
					) ) {
					throw new SQLException("badSchedule");
				}
			}
		}
		
		// inserting the record
		insertRecordAux(records);
	}
	
	private static boolean isOverlapping(List<TableRecord> registrations)
										 throws 
										 ClassNotFoundException, 
										 SQLException {
		
		String notOverlapping = QueryFactory.generateFindColliisons(registrations);
		int numNotOverlapping = 0;
		
		Connection con = createConnection();
		Statement stmt = null;
		ResultSet rs = null;
		
		List<String> courseNames = new LinkedList<String>();
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(notOverlapping);
			while(rs.next()) {
				String courseName = rs.getString("courseName");
				if(!courseNames.contains(courseName)) {
					courseNames.add(courseName);
					numNotOverlapping++;
				}
			}
		} finally {
			if(rs!=null) {
				rs.close();
			}
			if(stmt!=null){
				stmt.close();	
			}
			if(con!=null){
				con.close();
			}
		}
		
		return (numNotOverlapping != registrations.size());
	}
	
	private static synchronized void insertRegistration(List<TableRecord> records) 
														throws 
														ClassNotFoundException,
														SQLException {

		for(TableRecord record:records) {
			// checking the amount of registration to courses
			Registration reg = (Registration) record;
			int amountReg = DBManagment.getRecord(reg).size();
			
			Course getCapacity = new Course();
			getCapacity.courseName = reg.courseName;
			int capacity = DBManagment.getRecord(getCapacity).get(0).capacity;
			
			// if capacity reached throw exception
			if(amountReg == capacity) {
				throw new SQLException("Course Full");
			}
		}
		
		// checking that there are no overlaps between the hours
		if(isOverlapping(records)) {
			throw new SQLException("Courses Overlap");
		}
		
		insertRecordAux(records);
	}
	
	/**
	 * 
	 * @param records
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void updateRecord(List<TableRecord> records) 
														throws 
														ClassNotFoundException,
														SQLException {
		
		Connection con = createNonAtomicConnection();
		try {
			for(TableRecord record: records) {
				String update = QueryFactory.generateUpdateQuery(record);
				String searchName = record.getSearchName();
				Field searchNameField = null;
				try {
					searchNameField = record.getClass().getField(searchName);
				} catch (Exception e) {
					// ignore - cannot happen
				}
				
				// creating the array type
				String[] arrayType = record.getArrayType();
				List<String> listType = new LinkedList<String>(Arrays.asList(arrayType));
				listType.add(searchNameField.getType().
											getSimpleName());
				
				// creating the values array
				Object[] arrayValues = record.getArrayValues();
				List<Object> listValues = new LinkedList<Object>(Arrays.asList(arrayValues));
				try {
					listValues.add(searchNameField.get(record));
				} catch (Exception e) {
					// ignore - cannot happen
				}
				
				updatePrepStatement(listType.toArray(new String[]{}),
									listValues.toArray(new Object[]{}),
									update,con);
			}
			con.commit();
		} catch (SQLException e) {
			con.rollback();
			throw e;
		} finally {
			if(con!=null){
				con.close();
			}
		}
	}
	
	private static <T extends TableRecord> 
					T buildRecord(	ResultSet rs,
									T templateRecord) throws SQLException {
		
		Map<String,String> fieldTypes = templateRecord.getType();
		Map<String,Object> resultSetValues = new HashMap<String,Object>();
		
		// getting the values from the result set
		for(Entry<String, String> field: fieldTypes.entrySet()) {
			
			// extracting the information
			String type = field.getValue();
			Conversion converter = Conversion.valueOf(type); 
			Object value = converter.get(rs,field.getKey());
			resultSetValues.put(field.getKey(), value);
			
		}
		// building the record
		@SuppressWarnings("unchecked")
		T cloned = (T) templateRecord.clone();
		cloned.update(resultSetValues);
		
		return cloned;
	}
	
	/**
	 * Searches the record in the table specified by it.
	 * @param <T> a specific record of type that extends TableRecord
	 * @param record the passed parameter will be used as the search criteria
	 * @return list containing all the found records that match the search criteria
	 * @throws ClassNotFoundException
	 * @throws SQLException 
	 */
	public static 	<T extends TableRecord>
					List<T> getRecord(T record) 
													throws 
													ClassNotFoundException, 
													SQLException {
		
		String getRecord = QueryFactory.genereateGetRecordQuery(record);
		Map<String,String> fieldTypes = record.getType();
		Map<String,Object> values = record.getValues();	
		
		List<T> tableRecords = new LinkedList<T>(); 
		
		Connection con = createConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try { 
			
			// create a Statement class to execute the SQL statement 
			stmt = con.prepareStatement(getRecord);
			
			// putting the values in the query
			int i = 1;
			String searchName = record.getSearchName();
			String type = fieldTypes.get(searchName);
			Object value = values.get(searchName);
			Conversion converter = Conversion.valueOf(type); 
			converter.put(stmt,i,value);
			
			rs = stmt.executeQuery();
			while(rs.next()) { // we check if the result set isn't empty
				
				T cloned = buildRecord(rs,record);
				tableRecords.add(cloned);
				
			}
			
		} finally { // Close the connection. 
			if(rs!=null) {
				rs.close();
			}
			if(stmt!=null){
				stmt.close();	
			}
			if(con!=null){
				con.close();
			}
		}
		
		return tableRecords;
	}

	/**
	 * 
	 * @param <T>
	 * @param record
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static <T extends TableRecord> List<T> getTable(T record) 
													throws 
													ClassNotFoundException, 
													SQLException {
		
		String getTable = QueryFactory.generateGetTable(record.getTableName());
		
		List<T> tableRecords = new LinkedList<T>(); 
		
		Connection con = createConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try { 
			
			stmt = con.createStatement();
			rs = stmt.executeQuery(getTable);
			while(rs.next()) { // we check if the result set isn't empty
				
				T cloned = buildRecord(rs,record);
				tableRecords.add(cloned);
				
			}
			
		} finally { // Close the connection. 
			if(rs!=null) {
				rs.close();
			}
			if(stmt!=null){
				stmt.close();	
			}
			if(con!=null){
				con.close();
			}
		}
		
		return tableRecords;
	}
		
	
	private static void removeRecordAux(	TableRecord record,
											Connection con) 
														throws
														ClassNotFoundException, 
														SQLException {
		
		String removeRecord = QueryFactory.generateRemoveRecordQuery(record);
		String tableName = record.getClass().getSimpleName();
		
		// creating the type mapping of the fields: (var name,type)
		String[] fieldTypes = new String[1];
		String searchName = record.getSearchName();
		fieldTypes[0]=record.getType().get(searchName);;
		
		// creating the value mapping of the fields: (var name,value)
		Object[] values = new Object[1];
		Object value = record.getValues().get(searchName);
		values[0]=value;
		
		updatePrepStatement(fieldTypes, values, removeRecord, con);
		Monitor.write(null, "Removing a record from table:"+tableName);
	}
	
	private static void removeRecordsAux(List<TableRecord> records) 
														throws 
														SQLException, 
														ClassNotFoundException {
		
		Connection con = createNonAtomicConnection();
		try {
			for(TableRecord record: records) {
				
				removeRecordAux(record,con);
			}
			con.commit();
		} catch (SQLException e) {
			con.rollback();
			throw e;
		} finally { // Close the connection. 
			if(con!=null){
				con.close();
			}
		}
	}
	
	/**
	 * Removing the records in succession as they appear in the list.
	 * The atomicity means, that either all of the queries succeed or they all fail. 
	 * @param records the records from the tables we want to remove
	 * @throws ClassNotFoundException 
	 * @throws SQLException means that all of the queries in the transaction failed
	 */
	public static void removeRecord(List<TableRecord> records) 
															throws
															SQLException, 
															ClassNotFoundException {
		
		// we have a special handling for courses
		for(TableRecord record: records) {
			if(record instanceof Course) {
				removeCourse(records);
			}
		}
		
		removeRecordsAux(records);
	}
	
	private static synchronized void removeCourse(List<TableRecord> records) 
													throws 
													SQLException, 
													ClassNotFoundException {

		removeRecordsAux(records);
	}
	
	private static List<TableRecord> performJoin(	Course course,
													CourseSchedule schedule,
													String join) 
													throws 
													ClassNotFoundException,
													SQLException {
		
		List<TableRecord> joinRes = new LinkedList<TableRecord>();
		
		Connection con = createConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try { 
			
			// create a Statement class to execute the SQL statement 
			stmt = con.prepareStatement(join);
			
			rs = stmt.executeQuery();
			while(rs.next()) { // we check if the result set isn't empty
				
				Course clonedCourse = buildRecord(rs,course);
				joinRes.add(clonedCourse);
				CourseSchedule clonedSchedule = buildRecord(rs,schedule);
				joinRes.add(clonedSchedule);
				
			}
			
		} finally { // Close the connection. 
			if(rs!=null) {
				rs.close();
			}
			if(stmt!=null){
				stmt.close();	
			}
			if(con!=null){
				con.close();
			}
		}
		
		return joinRes;
		
	}
	
	/**
	 * 
	 * @param course
	 * @param schedule
	 * @return are ordered in pairs of course description and course schedule
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static List<TableRecord> getCourseDetails() 
													throws 
													ClassNotFoundException,
													SQLException {
		
		Course templateCourse = new Course();
		CourseSchedule templateCourseSchedule = new CourseSchedule();
		
		String join = QueryFactory.generateJoin();
		return performJoin(templateCourse,templateCourseSchedule,join);
	}
	
	public static List<TableRecord> getMyCourses(String userName) 
												throws
												ClassNotFoundException, 
												SQLException {
		
		Course templateCourse = new Course();
		CourseSchedule templateCourseSchedule = new CourseSchedule();
		
		String join = QueryFactory.generateGetMyCourses(userName);
		return performJoin(templateCourse,templateCourseSchedule,join);
	}
	
	/**
	 * 
	 * @return
	 */
	public static List<TableRecord> getAvailableCourses(User user) 
														throws 
														ClassNotFoundException,
														SQLException {
		Course templateCourse = new Course();
		CourseSchedule templateCourseSchedule = new CourseSchedule();
		
		String join = QueryFactory.generateAvailableCourses(user.userName);
		Monitor.write(null, "Using this query to get all the available courses:"+join);
		return performJoin(templateCourse,templateCourseSchedule,join);
	}
	
}
